﻿/*
 * 1. 完成Excel数据的入库操作
 * 
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace HousingDistribution
{
    class Excel2MySQL
    {
        public Excel2MySQL(FormExcelPreview.UpdateProgressBarDelegate updateFunc, ref DataTable errAddrPersonTable)
        {
            this.updateFunc = updateFunc;
            this.errAddrPersonTable = errAddrPersonTable;
        }

        public void ProcExcelData(DataTable dt)
        {
            if (Enum.IsDefined(typeof(PersonTypeEnum), dt.TableName))
            {
                PersonTypeEnum personType = (PersonTypeEnum) Enum.Parse(typeof(PersonTypeEnum), dt.TableName, true);

                if (personType != PersonTypeEnum.Unknown)
                    ProcPersonInfoFromExcel(personType, dt);
                else
                    ProcHouseFromExcel(dt);
            }
        }

        HousingInfoMatch houseinfo = new HousingInfoMatch();

        //从房屋excel表读入房屋数据到mysql。
        void ProcHouseFromExcel(DataTable houseTable)
        {
            string addr = null;
            string streetno = null;
            string buildingno = null;
            string doorway = null;
            string floor = null;
            string roomno = null;
            string insertHouseinfo = null;
            string housingType = null;
           // int housingArea = 0;
            string housingUse = null;
            string housingProperty = null;

            for (int i = 1; i < houseTable.Rows.Count; i++ )
            {
                this.updateFunc();
                
                addr = houseTable.Rows[i][0].ToString().Trim();
              
                //房屋格式为：27号院2栋1门1号。
                if (addr != null)
                {
                    streetno = addr.Substring(0, 2);
                }
                if ((addr != null) && (addr.IndexOf("栋") > 0))
                {
                    buildingno = addr.Substring(4, addr.IndexOf("栋") - 4);
                }
                if ((addr != null) && (addr.IndexOf("栋") > 0) && (addr.IndexOf("门") > 0))
                {
                    doorway = addr.Substring(addr.IndexOf("栋") + 1, addr.IndexOf("门") - addr.IndexOf("栋")-1 );
                }
                //if ((addr != null) && (addr.IndexOf("栋") > 0) && (addr.IndexOf("单元") > 0) && addr.IndexOf("层") > 0)
                //{
                //    floor = addr.Substring(addr.IndexOf("单元") + 2, addr.IndexOf("层") - addr.IndexOf("单元") - 2);
                //}
                if ((addr != null) && (addr.IndexOf("栋") > 0) && (addr.IndexOf("门") > 0) && addr.IndexOf("号") > 0)
                {
                    roomno = addr.Substring(addr.IndexOf("门") + 1, addr.Length - addr.IndexOf("门") - 2);
                }

                housingType = houseTable.Rows[i][1].ToString();
                int housingArea = Convert.ToInt16(houseTable.Rows[i][3]);
                housingUse = houseTable.Rows[i][4].ToString();
                housingProperty = houseTable.Rows[i][2].ToString();
        
                insertHouseinfo = "insert into housing (housingType, housingFullAddr, streetNo, buildingNo, doorWay, floor, roomNo, housingArea, housingUse, housingProperty) values ('" 
                    +housingType+ "','" +addr+ "','" +streetno+ "','" +buildingno+ "','" +doorway+ "','" +floor+ "','" +roomno+ "','" +housingArea+ "','" +housingUse+ "','" +housingProperty+ "')" ;
                DBProc.Instance().Excute(insertHouseinfo);
            }
        }

        //从人员excel读入数据到mysql。
        void ProcPersonInfoFromExcel(PersonTypeEnum personType, DataTable dataTable)
        {
            ObjectParser parser = null;

            if (personType == PersonTypeEnum.ArmMan)
                parser = new ArmyManParserFromExcel();
            else if (personType == PersonTypeEnum.Staff)
                parser = new StaffParserFromExcel();
            else if (personType == PersonTypeEnum.Worker)
                parser = new WorkerParserFromExcel();
            else if (personType == PersonTypeEnum.NCO)
                parser = new NCOParserFromExcel();
            else if (personType.Equals("退休"))
            {
            }
            else if (personType.Equals("其他"))
            {
            }
            else
            {
            }

            if (parser == null)
                return;
            
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                this.updateFunc();

                PersonInfo personInfo = parser.ParsePerson(dataTable.Rows[i]);
                
                personInfo.type = personType;

                //name == null 表明数据转换时有问题，则不处理
                if ((personInfo.name == null) || (personInfo.name == "")
                    || (personInfo.sex != "男" && personInfo.sex != "女"))
                    continue;

                //首先检查数据库中是否存在对应的记录
                if (infoSearcher.PersonExisted(personInfo))
                    continue;

                //其次，从数据库中查询匹配的住房信息
                HousingInfo housingInfo = parser.ParseHousing(dataTable.Rows[i]);
                int idhousing = housingInfoFromMySql.GetHousingIdFromMySql(housingInfo);

                if (personInfo.marital == PersonMaritalEnum.Married && PersonSortInfoProc.MateIsOurEmployee(personInfo.mateInfo) == true)
                {
                    personInfo.bothEmployee = 1;
                }
                else
                    personInfo.bothEmployee = 0;

                if (idhousing > 0)
                {
                    if (personInfo.housingInfo == null)
                        personInfo.housingInfo = new List<HousingOccupancyInfo>();

                    HousingOccupancyInfo info;
                    info.id = idhousing;
                    info.checkinTime = DateProc.InvalidDate();
                    personInfo.housingInfo.Add(info);
                }
                else if (housingInfo.addr.Length > 0)
                {
                    string streetno = housingInfoFromMySql.GetStreetNo(personInfo.curHousingAddr.addr);
                    if ((streetno == "27") || (streetno == "38") || (streetno == "48"))
                    {
                        DataRow newRow = this.errAddrPersonTable.NewRow();
                        newRow[0] = personInfo.name;
                        newRow[1] = personInfo.unit;
                        newRow[2] = housingInfo.addr;
                        this.errAddrPersonTable.Rows.Add(newRow);
                    }
                }

                string insertPersonInfoSql = sqlCreator.InsertSql(personInfo);

                personInfo.idperson = DBProc.Instance().ExcuteInsertSql(insertPersonInfoSql);

                sortInfoProc.Proc(personInfo);
            }
        }

        //sql 语句构造器
        DataStruct2SQL sqlCreator = new DataStruct2SQL();

        HousingInfoMatch housingInfoFromMySql = new HousingInfoMatch();

        InfoSearcher infoSearcher = new InfoSearcher();

        DataTable errAddrPersonTable;

        FormExcelPreview.UpdateProgressBarDelegate updateFunc;

        PersonSortInfoProc sortInfoProc = new PersonSortInfoProc();
    }
}
